<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tihexiao extends Commonwork
{
    public function vtime($time){
        $output = '';
        foreach (array(86400 => '天', 3600 => '小时', 60 => '分', 1 => '秒') as $key => $value) {
            if ($time >= $key) $output .= floor($time/$key) . $value;
            $time %= $key;
        }
        return $output;
    }
        public function shenhemx(){
            $workId = $this->request->param('workId');
        $trackId = $this->request->param('trackId');
        $myId = $this->request->param('myId');
        $id = db('cg_workflow')->where('id',$myId)->value('yw_id');
        //查询核销基本信息
        $basic = db('cg_ti_hexiao')->where('id',$id)->find();
        $basic['ctime'] = date('Y-m-d H:i',$basic['ctime']);
        $basic['cuser_name'] = db('employee')->where('id',$basic['cuser'])->value('em_name');
        $basic['cuser_department'] = db('department_employee t')->join('department t2','t2.id=t.department_id')->where(['employee_id'=>$basic['cuser'],'t.index'=>1])->value('t2.de_name');
        $basic['cuser_job'] = db('job_employee t')->join('job t2','t2.id=t.job_id')->where(['employee_id'=>$basic['cuser']])->value('t2.job_name');
        $basic['jxs_title'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('title');
        $basic['jxs_direct'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('direct');
        $basic['jxs_cw_bank'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_bank');
        $basic['jxs_cw_banksn'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_banksn');
        $this->assign('basic',$basic);

        $list = db('cg_ti_hexiao_list t')->join('cg_ti_list t2','t2.id=t.list_id')->join('cg_ti t3','t3.id=t2.ti_id')->where('t.hx_id',$id)->field('*,t.sn tsn,t2.sn t2sn,t3.sn t3sn,t.id tid,t2.id t2id,t3.id t3id,t2.cuser ysqr,t3.type_id')->select();
       // print_r($list);
        $shijitourujine = 0;
        $yihexiaojine = 0;
        $weishuihexiaojine = 0;
        $shuijin = 0;
        $hanshuihexiaojine = 0;
        $shijitourujine2 = 0;
        foreach($list as $k=>$v){
            $shijitourujine += $v['fb_shijiweishui'];
            $yihexiaojine += $v['hx_yihexiao'];
            $weishuihexiaojine += $v['weishuihexiaojine'];
            $shuijin += $v['shuijin'];
            $hanshuihexiaojine += $v['hanshuihexiaojine'];
            $shijitourujine2 += $v['shijitourujine'];
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_id'] = db('cg_net')->where('id',$v['net_id'])->value('system_id');
            $v['system_title'] = db('cg_system')->where('id',$v['system_id'])->value('title');
            $v['brand_title'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            if($v['category']){
                $brand_id = db('pro_category')->where('id',$v['category'])->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['series']){
                $category_id = db('pro_series')->where('id',$v['series'])->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['sku']){
                $series_id = db('pro_sku')->where('id',$v['sku'])->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['product']){
                $sku_id = db('pro_products')->where('id',$v['product'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['tm_id']){
                $sku_id = db('pro_products')->where('id',$v['tm_id'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['feetype']){
                $v['feetype_title'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            }
            $v['shijitourujine_title'] = db('cg_paymethod')->where('id',$v['zhifufangshi'])->value('title');
            $v['ysqr_title'] = db('employee')->where('id',$v['ysqr'])->value('em_name');
            $list[$k] = $v;
        }
        $this->assign('shijitourujine',$shijitourujine);
        $this->assign('yihexiaojine',$yihexiaojine);
        $this->assign('weishuihexiaojine',$weishuihexiaojine);
        $this->assign('shuijin',$shuijin);
        $this->assign('hanshuihexiaojine',$hanshuihexiaojine);
        $this->assign('shijitourujine2',$shijitourujine2);
        $this->assign('list',$list);
        $logs=$this->getlog(18,$id);
        $zhifu=db("cg_ti_hexiao_zhifu")->where(["hx_id"=>$id])->select();
        for($i=0;$i<count($zhifu);$i++){
            $zhifu[$i]["user"]=db("employee")->where(["id"=>$zhifu[$i]["zfr"]])->value("em_name");
        }
        $this->assign("zhifu",$zhifu);
        $this->assign("logs",$logs);
        return $this->fetch();
    }

    public function shenhe(){
        //$this->assign("from",$from);
        $workId = $this->request->param('workId');
        $trackId = $this->request->param('trackId');
        $myId = $this->request->param('myId');
        $heid =db('cg_workflow')->where('id',$myId)->value('yw_id');

        $this->assign('heid',$heid);
        $info=db("cg_ti_hexiao")->where('id',$heid)->find();
        $info["jxs"]=db("cg_jxs")->where('id',$info["jxs_id"])->value("title");
        $info["user"]=db("employee")->where(["id"=>$info["cuser"]])->value("em_name");
        $info["de_name"]=db("department_employee")->alias("de")->join("department d","d.id=de.department_id")->where("de.employee_id={$info["cuser"]} and de.index=1")->value("de_name");
        $jxs=db("cg_jxs")->where('id',$info["jxs_id"])->find();
        $info["zoning"]=db("cg_market_zoning")->where(["id"=>$jxs["zoning"]])->value("title");
        $info["provice"]=db("cg_market_provice")->where(["id"=>$jxs["provice"]])->value("title");
        $info["city"]=db("cg_market_city")->where(["id"=>$jxs["city"]])->value("title");
       
        $de=db("cg_ti_hexiao_list")->alias("hd")->join("cg_ti_list d","hd.list_id=d.id")->where("hd.hx_id=$heid")
        ->field("hd.weishuihexiaojine,d.hx_byhx,d.hx_wsq,d.hx_yihexiao")->select();
        $info["weishui"]=0;$info["buhe"]=0;$info["wsq"]=0;$info["yh"]=0;
        $info["wanquan"]=0;$info["weihewan"]=0;$info["buyuhe"]=0;
        for($i=0;$i<count($de);$i++){
            if($de[$i]["weishuihexiaojine"])
                $info["weishui"]+=$de[$i]["weishuihexiaojine"];
            if($de[$i]["hx_byhx"])
                $info["buhe"]+=$de[$i]["hx_byhx"];
            if($de[$i]["hx_wsq"]&&!$de[$i]["hx_byhx"])
                $info["wsq"]+=$de[$i]["hx_wsq"];
            if($de[$i]["hx_yihexiao"])
                $info["yh"]+=$de[$i]["hx_yihexiao"];
            if($de[$i]["hx_wsq"]==0&&!$de[$i]["hx_byhx"]){
                $info["wanquan"]++;
            }else if($de[$i]["hx_byhx"]){
                $info["buyuhe"]++;
            }else{
                $info["weihewan"]++;
            }
        }
        $info["ke"]=$info["yh"]+$info["wsq"]+$info["buhe"]+$info["weishui"];

        $fei=db("cg_ti_hexiao_list")->alias("hd")->join("cg_paymethod p","hd.zhifufangshi=p.id")
        ->group("hd.zhifufangshi")->where("hd.hx_id=$heid")->field("p.title,count(hd.id) as c")->select();
        $this->assign("fei",$fei);
        $feed=db("cg_ti_hexiao_list")->alias("hd")
        ->join("cg_ti_list d","hd.list_id=d.id")->join("cg_feiyongkemu3 f3","f3.id=d.feetype")->group("d.feetype")->where("hd.hx_id=$heid")
        ->field("f3.title,count(hd.id) as c,sum(hd.weishuihexiaojine) as benci,sum(d.hx_byhx) as buhe ,sum(d.hx_yihexiao) as yihe,sum(d.hx_wsq) as wsq,sum(fb_shijiweishui) as kehe")->select();
        for($i=0;$i<count($feed);$i++){
            if($feed[$i]["buhe"])
                $feed[$i]["shengyu"]=$feed[$i]["wsq"]-$feed[$i]["buhe"];
            else
                $feed[$i]["shengyu"]=$feed[$i]["wsq"];
        }
        $this->assign("feed",$feed);
         $net=db("cg_ti_hexiao_list")->alias("hd")
        ->join("cg_ti_list d","hd.list_id=d.id")->join("cg_net n","n.id=d.net_id")->join("cg_feiyongkemu3 f3","f3.id=d.feetype")->group("d.net_id,d.feetype")->where("hd.hx_id=$heid")
        ->field("n.title,f3.title as fei,count(hd.id) as c,sum(hd.weishuihexiaojine) as benci,sum(d.hx_byhx) as buhe,sum(d.hx_yihexiao) as yihe,sum(d.hx_wsq) as wsq,sum(fb_shijiweishui) as kehe")->select();
        for($i=0;$i<count($net);$i++){
            if($net[$i]["buhe"])
                $net[$i]["shengyu"]=$net[$i]["wsq"]-$net[$i]["buhe"];
            else
                $net[$i]["shengyu"]=$net[$i]["wsq"];
        }
        $this->assign("net",$net);
        $t=time();
        $cainian=db("cg_cainian")->where("starttime<=$t and endtime>=$t")->find();//print_r($cainian);exit;

        $info["sqz"]=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.status in (2,3,4,5) and t.jxs_id={$jxs["id"]}")->group("t.jxs_id")->field("count(td.id) as c,sum(yjsjtrfy) as jine")->find();
        $info["pz"]=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.status in (7) and t.jxs_id={$jxs["id"]} and (t.status2 is null or t.status2=0)")->group("t.jxs_id")->field("count(td.id) as c,sum(yjsjtrfy) as jine")->find();
        $info["hxz"]=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.status in (7) and t.jxs_id={$jxs["id"]} and (t.status2 is null or t.status2=0) and td.hx_status in (1,2)")->group("t.jxs_id")->field("count(td.id) as c,sum(hx_sqz) as jine")->find();
       
        if(!$info["hxz"]){
            $info["hxz"]["c"]=0;
            $info["hxz"]["jine"]=0;
        }else{
            $info["hxz"]["c"]=count($info["hxz"]);
             $info["hxz"]["jine"]=round($info["hxz"]["jine"],2);
        }
        //echo db("cg_ti_list")->getLastsql();exit;
        $info["whx"]=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.status in (7) and t.jxs_id={$jxs["id"]} and (t.status2 is null or t.status2=0) and td.hx_status in (3,0,5)")->group("t.jxs_id")->field("count(td.id) as c,sum(yjsjtrfy) as jine")->find();
        if($info["whx"])
            $info["whx"]["jine"]=round($info["whx"]["jine"],2);
        else
            $info["whx"]["jine"]=0;$info["whx"]["c"]=0;
        $info["bhx"]=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.status in (7) and t.jxs_id={$jxs["id"]} and (t.status2 is null or t.status2=0) and td.hx_status in (2)")->group("t.jxs_id")->field("count(td.id) as c,sum(hx_byhx) as jine")->find();
        if($info["bhx"])
            $info["bhx"]["jine"]=round($info["bhx"]["jine"],2);
        else{
            $info["bhx"]["jine"]=0;$info["bhx"]["c"]=0;
        }
        //echo db("cg_ti_list")->getLastsql();exit;
        $pay=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->join("cg_paymethod p","p.id=td.paymethod")
        ->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.status in (7) and t.jxs_id={$jxs["id"]}")
        ->group("td.paymethod")
        ->field("p.title,sum(td.yjsjtrfy) as jine")->select();
        $this->assign("pay",$pay);
        $zongyuji=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")
        ->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.type_id in (0,2) and t.status in (7) and t.jxs_id={$jxs["id"]}")->sum("yjsjtrfy");
        $zongxl=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")
        ->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.type_id in (0,2) and t.status in (7) and t.jxs_id={$jxs["id"]}")->sum("totalExpectedSales");
        if($zongyuji&&$zongxl)
            $info["yfyl"]=round(($zongyuji/$zongxl)*100,2);
        else
            $info["yfyl"]=0;
        $fbyuji=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")
        ->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.type_id in (0,2) and t.status in (7) and t.jxs_id={$jxs["id"]}")->sum("fb_shijitourufeiyong");
        $fbxl=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")
        ->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]} and t.type_id in (0,2) and t.status in (7) and t.jxs_id={$jxs["id"]}")->sum("fb_shijixiaoliang");
        if($fbyuji&&$fbxl)
            $info["ffyl"]=round(($fbyuji/$fbxl)*100,2);
        else
            $info["ffyl"]=0;
        $info["cha"]=$info["ffyl"]-$info["yfyl"];
        $this->assign("info",$info);
        $feiyear=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","k3.id=td.feetype")
        ->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]}  and t.status in (2,3,4,5,6,7) and t.jxs_id={$jxs["id"]}")
        ->group("k3.id")
        ->field("k3.title,sum(yjsjtrfy) as jine")->select();
        $zongshu=0;
        for($i=0;$i<count($feiyear);$i++){
            $zongshu+=$feiyear[$i]["jine"];
        }  
        for($i=0;$i<count($feiyear);$i++){
            $feiyear[$i]["bili"]=round($feiyear[$i]["jine"]/$zongshu*100,2);
        }  
        $this->assign("feiyear",$feiyear);
         $feiyear2=db("cg_ti_list")->alias("td")->join("cg_ti t","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","k3.id=td.feetype")
        ->where("t.ctime>={$cainian["starttime"]} and t.ctime<={$cainian["endtime"]}  and t.status in (7) and (td.hx_yihexiao is not null and td.hx_yihexiao>0) and t.jxs_id={$jxs["id"]}")
        ->group("k3.id")
        ->field("k3.title,sum(td.hx_yihexiao) as jine")->select();
        $zongshu=0;
        for($i=0;$i<count($feiyear2);$i++){
            $zongshu+=$feiyear2[$i]["jine"];
        }  
        for($i=0;$i<count($feiyear2);$i++){
            $feiyear2[$i]["bili"]=round($feiyear2[$i]["jine"]/$zongshu*100,2);
        }  
        $this->assign("feiyear2",$feiyear2);
         //本次申请已耗时
        $time = time()-$info['ctime'];
        $bcsqhs = $this->vtime($time);
        $this->assign('bcsqhs',$bcsqhs);

        //获取操作记录
        $post_data = [];
        $post_data['accessToken'] = session('accessToken');
        $post_data['workId'] = $workId;
        $czjl = $this->curl_post(config('workflow_url').'/horizon/workflow/rest/flow/support/history.wf',$post_data);
        $czjl = object_array($czjl);
//        echo "<pre>";print_r($czjl);
        foreach($czjl as $k=>$v){
            //查询审批意见
            $wf_id = 0;
//            $v['spyj'] = db('cg_workflow_spyj')->where(['wf_id'=>$wf_id,'spr_id']);
        }
        $this->assign('czjl',$czjl);
          //查询附件
        $attachs = db('cg_workflow_spyj')->where('wf_id',$myId)->column('attach');
        if($attachs){
            $attach = [];
            foreach($attachs as $item){
                if($item){
                    $item = unserialize($item);
                    foreach($item as $k=>$v){
                        $temp = Db::table('annex')->where(array('id'=>$v))->find();
                        $temp['path'] = '/uploads/'.$temp['path'];
                        $item[$k] = $temp;
                    }
                    $attach = array_merge($attach,$item);
                }
            }

            $this->assign('attach',$attach);

        }
        return $this->fetch();
    }
    //提交工作流
    public function tijiao($id){
        $jieguo=$this->gowork(18,$id);
                if($jieguo==json_encode(['state'=>1,'msg'=>'提交成功'])){
                    Db::commit();
                    return $jieguo;
                }else{
                    Db::rollBack();
                    return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                }
    }
	//核销列表
    public function index($page=1,$list=20)
    {
        $request = Request::instance();
        $data = $request->post();
        $eid=Session::get("employee_id");
        $where = "t.cuser=$eid ";
        if ($data) {
            if ($data["no"])
                $where .= "t.sn={$data["no"]} ";
            if (trim($data["title"]))
                $where .= "and j.title='%{$data["title"]}%' ";
            if ($data["status"])
                $where .= "and t.status={$data["status"]} ";
            if (trim($data["start"])) {
                $st = strtotime($data["start"]);
                $where .= "and t.ctime>=$st ";
            }
            if (trim($data["end"])) {
                $ed = strtotime($data["end"]) + 24 * 3600;
                $where .= "and t.ctime<$ed ";
            }
        } else {
            $data = ["no" => "", "title" => "", "status" => "", "start" => "", "end" => ""];
        }

        $sum = Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")
            ->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $applys = Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")
            ->order("t.ctime desc")
            ->where($where)->limit($start,$list)
            ->field("t.*,j.title")->select();

//        echo Db::table('cg_ti')->getLastSql();
        
        foreach($applys as $k=>$v){
            $v['cuser_title'] = Db::table('employee')->where(array('id'=>$v['cuser']))->value('em_name');
            $v["state"]=db("ti_state")->where(["id"=>$v["status"]])->value("title");
            $v["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$v["id"],"ywlx"=>18])->value("id");
            $applys[$k] = $v;
        }

       $this->assign("applys", $applys);

        $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);

        return $this->fetch();
        //return $this->fetch();
    }

    /**
     * 核销提交工作流
     * @throws \think\Exception
     */
    public function submitWorkflow(){
        $id = $this->request->param('id');
        if(!$id){
            echo  json_encode([
                "success"=>false,
                "msg"=>"参数错误"
            ]);die;
        }

        $bool = Db::table('cg_ti_hexiao')
            ->where(array('id'=>$id))
            ->update([
                'status'=>3
            ]);
        if(false !== $bool){
            echo  json_encode([
                "success"=>true,
                "msg"=>"提交成功"
            ]);die;
        }else{
            echo  json_encode([
                "success"=>false,
                "msg"=>"提交失败"
            ]);die;
        }
    }

    public function view(){
        $id = $this->request->param('id');
        //查询核销基本信息
        $basic = db('cg_ti_hexiao')->where('id',$id)->find();
        $basic['ctime'] = date('Y-m-d H:i',$basic['ctime']);
        $basic['cuser_name'] = db('employee')->where('id',$basic['cuser'])->value('em_name');
        $basic['cuser_department'] = db('department_employee t')->join('department t2','t2.id=t.department_id')->where(['employee_id'=>$basic['cuser'],'t.index'=>1])->value('t2.de_name');
        $basic['cuser_job'] = db('job_employee t')->join('job t2','t2.id=t.job_id')->where(['employee_id'=>$basic['cuser']])->value('t2.job_name');
        $basic['jxs_title'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('title');
        $basic['jxs_direct'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('direct');
        $basic['jxs_cw_bank'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_bank');
        $basic['jxs_cw_banksn'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_banksn');
        $this->assign('basic',$basic);

        $list = db('cg_ti_hexiao_list t')->join('cg_ti_list t2','t2.id=t.list_id')->join('cg_ti t3','t3.id=t2.ti_id')->where('t.hx_id',$id)->field('*,t.sn tsn,t2.sn t2sn,t3.sn t3sn,t.id tid,t2.id t2id,t3.id t3id,t2.cuser ysqr,t3.type_id')->select();
       // print_r($list);
        $shijitourujine = 0;
        $yihexiaojine = 0;
        $weishuihexiaojine = 0;
        $shuijin = 0;
        $hanshuihexiaojine = 0;
        $shijitourujine2 = 0;
        foreach($list as $k=>$v){
            $shijitourujine += $v['fb_shijiweishui'];
            $yihexiaojine += $v['hx_yihexiao'];
            $weishuihexiaojine += $v['weishuihexiaojine'];
            $shuijin += $v['shuijin'];
            $hanshuihexiaojine += $v['hanshuihexiaojine'];
            $shijitourujine2 += $v['shijitourujine'];
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_id'] = db('cg_net')->where('id',$v['net_id'])->value('system_id');
            $v['system_title'] = db('cg_system')->where('id',$v['system_id'])->value('title');
            $v['brand_title'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            if($v['category']){
                $brand_id = db('pro_category')->where('id',$v['category'])->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['series']){
                $category_id = db('pro_series')->where('id',$v['series'])->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['sku']){
                $series_id = db('pro_sku')->where('id',$v['sku'])->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['product']){
                $sku_id = db('pro_products')->where('id',$v['product'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['tm_id']){
                $sku_id = db('pro_products')->where('id',$v['tm_id'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['feetype']){
                $v['feetype_title'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            }
            $v['shijitourujine_title'] = db('cg_paymethod')->where('id',$v['zhifufangshi'])->value('title');
            $v['ysqr_title'] = db('employee')->where('id',$v['ysqr'])->value('em_name');
            $list[$k] = $v;
        }
        $this->assign('shijitourujine',$shijitourujine);
        $this->assign('yihexiaojine',$yihexiaojine);
        $this->assign('weishuihexiaojine',$weishuihexiaojine);
        $this->assign('shuijin',$shuijin);
        $this->assign('hanshuihexiaojine',$hanshuihexiaojine);
        $this->assign('shijitourujine2',$shijitourujine2);
        $this->assign('list',$list);
        $logs=$this->getlog(18,$id);
        $zhifu=db("cg_ti_hexiao_zhifu")->where(["hx_id"=>$id])->select();
        for($i=0;$i<count($zhifu);$i++){
            $zhifu[$i]["user"]=db("employee")->where(["id"=>$zhifu[$i]["zfr"]])->value("em_name");
        }
        $this->assign("zhifu",$zhifu);
        $this->assign("logs",$logs);
        return $this->fetch();
    }

    //导出view
    public function exportview(){
        $id = $this->request->param('id');
        //查询核销基本信息
        $basic = db('cg_ti_hexiao')->where('id',$id)->find();
        $basic['ctime'] = date('Y-m-d H:i',$basic['ctime']);
        $basic['cuser_name'] = db('employee')->where('id',$basic['cuser'])->value('em_name');
        $basic['cuser_department'] = db('department_employee t')->join('department t2','t2.id=t.department_id')->where(['employee_id'=>$basic['cuser'],'t.index'=>1])->value('t2.de_name');
        $basic['cuser_job'] = db('job_employee t')->join('job t2','t2.id=t.job_id')->where(['employee_id'=>$basic['cuser']])->value('t2.job_name');
        $basic['jxs_title'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('title');
        $basic['jxs_direct'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('direct');
        $basic['jxs_cw_bank'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_bank');
        $basic['jxs_cw_banksn'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_banksn');
        $this->assign('basic',$basic);

        $list = db('cg_ti_hexiao_list t')->join('cg_ti_list t2','t2.id=t.list_id')->join('cg_ti t3','t3.id=t2.ti_id')->where('t.hx_id',$id)->field('*,t.sn tsn,t2.sn t2sn,t3.sn t3sn,t.id tid,t2.id t2id,t3.id t3id,t2.cuser ysqr')->select();
        $shijitourujine = 0;
        $yihexiaojine = 0;
        $weishuihexiaojine = 0;
        $shuijin = 0;
        $hanshuihexiaojine = 0;
        $shijitourujine2 = 0;
        foreach($list as $k=>$v){
            $shijitourujine += $v['fb_shijiweishui'];
            $yihexiaojine += $v['hx_yihexiao'];
            $weishuihexiaojine += $v['weishuihexiaojine'];
            $shuijin += $v['shuijin'];
            $hanshuihexiaojine += $v['hanshuihexiaojine'];
            $shijitourujine2 += $v['shijitourujine'];
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_id'] = db('cg_net')->where('id',$v['net_id'])->value('system_id');
            $v['system_title'] = db('cg_system')->where('id',$v['system_id'])->value('title');
            $v['brand_title'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            if($v['category']){
                $brand_id = db('pro_category')->where('id',$v['category'])->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['series']){
                $category_id = db('pro_series')->where('id',$v['series'])->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['sku']){
                $series_id = db('pro_sku')->where('id',$v['sku'])->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['product']){
                $sku_id = db('pro_products')->where('id',$v['product'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['tm_id']){
                $sku_id = db('pro_products')->where('id',$v['tm_id'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['feetype']){
                $v['feetype_title'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            }
            $v['shijitourujine_title'] = db('cg_paymethod')->where('id',$v['zhifufangshi'])->value('title');
            $v['ysqr_title'] = db('employee')->where('id',$v['ysqr'])->value('em_name');
            $list[$k] = $v;
        }

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("销售费用核销申请");

        #################总标题
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $n = $objPHPExcel->getActiveSheet()->getHighestRow();
        $objPHPExcel->getActiveSheet()->getStyle('A1:W'.$n)->applyFromArray($styleArray);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:W1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',"销售费用核销申请");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:W2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{".db('employee')->where('id',session('employee_id'))->value('em_name')."})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(11);
        #################申请基本信息
        $objPHPExcel->getActiveSheet()->mergeCells('A3:B3');
        $objPHPExcel->getActiveSheet()->setCellValue('A3','申请人');
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(11);
        //填充背景色
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
        $objPHPExcel->getActiveSheet()->setCellValue('C3',$basic['cuser_name']);
        $objPHPExcel->getActiveSheet()->getStyle('C3')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('E3:F3');
        $objPHPExcel->getActiveSheet()->setCellValue('E3','所属部门/职位');
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFont()->setSize(11);
        //填充背景色
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->mergeCells('G3:H3');
        $objPHPExcel->getActiveSheet()->setCellValue('G3',$basic['cuser_department'].'/'.$basic['cuser_job']);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('I3:J3');
        $objPHPExcel->getActiveSheet()->setCellValue('I3','面单编号');
        $objPHPExcel->getActiveSheet()->getStyle('I3')->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle('I3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('I3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $objPHPExcel->getActiveSheet()->mergeCells('K3:L3');
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K3',$basic['sn'],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->getStyle('K3')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('M3:N3');
        $objPHPExcel->getActiveSheet()->setCellValue('M3','提交时间');
        $objPHPExcel->getActiveSheet()->getStyle('M3')->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle('M3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('M3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $objPHPExcel->getActiveSheet()->mergeCells('O3:P3');
        $objPHPExcel->getActiveSheet()->setCellValue('O3',$basic['ctime']);
        $objPHPExcel->getActiveSheet()->getStyle('O3')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('A4:B4');
        $objPHPExcel->getActiveSheet()->setCellValue('A4','经销商名称');
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getFill()->getStartColor()->setARGB('0099CCFF');
        $objPHPExcel->getActiveSheet()->mergeCells('C4:D4');
        $objPHPExcel->getActiveSheet()->setCellValue('C4',$basic['jxs_title']);
        $objPHPExcel->getActiveSheet()->getStyle('C4')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('E4:F4');
        $objPHPExcel->getActiveSheet()->setCellValue('E4','开户行');
        $objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle('E4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('E4')->getFill()->getStartColor()->setARGB('0099CCFF');
        $objPHPExcel->getActiveSheet()->mergeCells('G4:G4');
        $objPHPExcel->getActiveSheet()->setCellValue('G4',$basic['jxs_cw_bank']?$basic['jxs_cw_bank']:'待补录');
        $objPHPExcel->getActiveSheet()->getStyle('G4')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('I4:J4');
        $objPHPExcel->getActiveSheet()->setCellValue('I4','银行账号');
        $objPHPExcel->getActiveSheet()->getStyle('I4')->getFont()->setSize(11);
        $objPHPExcel->getActiveSheet()->getStyle('I4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('I4')->getFill()->getStartColor()->setARGB('0099CCFF');
        $objPHPExcel->getActiveSheet()->mergeCells('K4:L4');
        $objPHPExcel->getActiveSheet()->setCellValue('K4',$basic['jxs_cw_banksn']?$basic['jxs_cw_banksn']:'待补录');
        $objPHPExcel->getActiveSheet()->getStyle('K4')->getFont()->setSize(11);

        #########明细标题
        $objPHPExcel->getActiveSheet()->getStyle('A6:W8')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A6:W8')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->getRowDimension('A6')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A6:W6');
        $objPHPExcel->getActiveSheet()->setCellValue('A6','销售费用核销申请');
        $objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setSize(11);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->mergeCells('A7:A8');
        $objPHPExcel->getActiveSheet()->setCellValue('A7','明细编号');
        $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('B7:B8');
        $objPHPExcel->getActiveSheet()->setCellValue('B7','原申请人');
        $objPHPExcel->getActiveSheet()->getStyle('B7')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('C7:C8');
        $objPHPExcel->getActiveSheet()->setCellValue('C7','原申请文件');
        $objPHPExcel->getActiveSheet()->getStyle('C7')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('D7:D8');
        $objPHPExcel->getActiveSheet()->setCellValue('D7','原申请明细编号');
        $objPHPExcel->getActiveSheet()->getStyle('D7')->getFont()->setSize(11);

        $objPHPExcel->getActiveSheet()->mergeCells('E7:E8');
        $objPHPExcel->getActiveSheet()->setCellValue('E7','网点名称');
        $objPHPExcel->getActiveSheet()->mergeCells('F7:F8');
        $objPHPExcel->getActiveSheet()->setCellValue('F7','系统名称');
        $objPHPExcel->getActiveSheet()->mergeCells('G7:G8');
        $objPHPExcel->getActiveSheet()->setCellValue('G7','品牌');
        $objPHPExcel->getActiveSheet()->mergeCells('H7:H8');
        $objPHPExcel->getActiveSheet()->setCellValue('H7','三级费用科目');
        $objPHPExcel->getActiveSheet()->mergeCells('I7:K7');
        $objPHPExcel->getActiveSheet()->setCellValue('I7','实际投入');
        $objPHPExcel->getActiveSheet()->mergeCells('L7:S7');
        $objPHPExcel->getActiveSheet()->setCellValue('L7','本次核销申请');
        $objPHPExcel->getActiveSheet()->setCellValue('I8','实际投入金额');
        $objPHPExcel->getActiveSheet()->setCellValue('J8','已核销金额');
        $objPHPExcel->getActiveSheet()->setCellValue('K8','未核销金额');
        $objPHPExcel->getActiveSheet()->setCellValue('L8','未税核销金额');
        $objPHPExcel->getActiveSheet()->setCellValue('M8','有无发票');
        $objPHPExcel->getActiveSheet()->setCellValue('N8','发票号');
        $objPHPExcel->getActiveSheet()->setCellValue('O8','是否增值税');
        $objPHPExcel->getActiveSheet()->setCellValue('P8','税率');
        $objPHPExcel->getActiveSheet()->setCellValue('Q8','税金');
        $objPHPExcel->getActiveSheet()->setCellValue('R8','含税核销金额');
        $objPHPExcel->getActiveSheet()->setCellValue('S8','实际投入金额');
        $objPHPExcel->getActiveSheet()->mergeCells('T7:T8');
        $objPHPExcel->getActiveSheet()->setCellValue('T7','支付方式');
        $objPHPExcel->getActiveSheet()->mergeCells('U7:U8');
        $objPHPExcel->getActiveSheet()->setCellValue('U7','操作记录');
        $objPHPExcel->getActiveSheet()->mergeCells('V7:V8');
        $objPHPExcel->getActiveSheet()->setCellValue('V7','活动开始日期');
        $objPHPExcel->getActiveSheet()->mergeCells('W7:W8');
        $objPHPExcel->getActiveSheet()->setCellValue('W7','备注');

        #######################################明细
        $ye=8;
        foreach($list as $k=>$v){
            $ye++;
            $objPHPExcel->getActiveSheet()->getStyle($ye)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($ye)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($ye)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getRowDimension($ye)->setRowHeight(50);
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$ye,$v['sn'].'_'.$v['tsn']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$ye,$v['ysqr_title']);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$ye,$v['t3sn'],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$ye,$v['t3sn'].'_'.$v['t2sn']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$ye,$v['net_title']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$ye,$v["system_title"]);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$ye,$v["brand_title"]);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$ye,$v["feetype_title"]);
            $objPHPExcel->getActiveSheet()->setCellValue('I'.$ye,$v["fb_shijiweishui"]);
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$ye,$v['hx_yihexiao']);
            $objPHPExcel->getActiveSheet()->setCellValue('K'.$ye,$v["fb_shijiweishui"]-$v['hx_yihexiao']);
            $objPHPExcel->getActiveSheet()->setCellValue('L'.$ye,$v["weishuihexiaojine"]);
            $objPHPExcel->getActiveSheet()->setCellValue('M'.$ye,$v["youwufapiao"]==0?'无':'有');
            $objPHPExcel->getActiveSheet()->setCellValue('N'.$ye,$v["fapiaohao"]);
            $objPHPExcel->getActiveSheet()->setCellValue('O'.$ye,$v["shizengzhishui"]?'是':'否');
            $objPHPExcel->getActiveSheet()->setCellValue('P'.$ye,$v["shuilv"].'%');
            $objPHPExcel->getActiveSheet()->setCellValue('Q'.$ye,$v["shuijin"]);
            $objPHPExcel->getActiveSheet()->setCellValue('R'.$ye,$v["hanshuihexiaojine"]);
            $objPHPExcel->getActiveSheet()->setCellValue('S'.$ye,$v["shijitourujine"]);
            $objPHPExcel->getActiveSheet()->setCellValue('T'.$ye,$v["shijitourujine_title"]);
            $objPHPExcel->getActiveSheet()->setCellValue('U'.$ye,'');
            $objPHPExcel->getActiveSheet()->setCellValue('V'.$ye,date('Y-m-d',$v['huodongkaishiriqi']));
            $objPHPExcel->getActiveSheet()->setCellValue('W'.$ye,$v['beizhu']);
        }
        $objPHPExcel->getActiveSheet()->mergeCells('A'.(9+count($list)).":H".(9+count($list)));
        $objPHPExcel->getActiveSheet()->setCellValue('A'.(9+count($list)),'合计');
        $objPHPExcel->getActiveSheet()->setCellValue('I'.(9+count($list)),$shijitourujine);
        $objPHPExcel->getActiveSheet()->setCellValue('J'.(9+count($list)),$yihexiaojine);
        $objPHPExcel->getActiveSheet()->setCellValue('K'.(9+count($list)),($shijitourujine-$yihexiaojine));
        $objPHPExcel->getActiveSheet()->setCellValue('L'.(9+count($list)),$weishuihexiaojine);
        $objPHPExcel->getActiveSheet()->setCellValue('Q'.(9+count($list)),$shuijin);
        $objPHPExcel->getActiveSheet()->setCellValue('R'.(9+count($list)),$hanshuihexiaojine);
        $objPHPExcel->getActiveSheet()->setCellValue('S'.(9+count($list)),$shijitourujine2);

        $objPHPExcel->getActiveSheet()->setCellValue('A'.(10+count($list)),'备注');
        $objPHPExcel->getActiveSheet()->mergeCells('B'.(10+count($list)).':W'.(10+count($list)));
        $objPHPExcel->getActiveSheet()->setCellValue('B'.(10+count($list)),$basic['beizhu']);

       $ye=10+count($list);
       $ye+=2;
        $logs=$this->getlog(18,$id);
        $zhifu=db("cg_ti_hexiao_zhifu")->where(["hx_id"=>$id])->select();
        for($i=0;$i<count($zhifu);$i++){
            $zhifu[$i]["user"]=db("employee")->where(["id"=>$zhifu[$i]["zfr"]])->value("em_name");
        }
        $objPHPExcel->getActiveSheet()->mergeCells('A'.$ye.':W'.$ye);
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$ye,'支付信息');
        for($i=0;$i<count($zhifu);$i++){
            $ye++;$z=$zhifu[$i];
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$ye.':W'.$ye);
            $str="支付凭证：{$z["zfpz"]} 支付日期：{$z["zfsj"]} 支付金额：{$z["bczfje"]}元 支付人：{$z["user"]}";
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$ye,$str);
        }
        $ye+=2;
        $h=$ye;
             $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"审批意见");
        $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:Y{$h}");
         $objPHPExcel->getActiveSheet()->getStyle("A{$h}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle("A{$h}")->getFill()->getStartColor()->setARGB('0099CCFF');
        
         for($i=0;$i<count($logs);$i++){
            $h++;
            if($logs[$i]["COMMENTS"])
                $str="{$logs[$i]["USERNAME"]} 审批动作：{$logs[$i]["MEMO"]}；审批意见：{$logs[$i]["COMMENTS"]}(审批时间：{$logs[$i]["ACTIONTIME"]})";
            else
                $str="{$logs[$i]["USERNAME"]} 审批动作：{$logs[$i]["MEMO"]}；审批意见：无 (审批时间：{$logs[$i]["ACTIONTIME"]})";
             $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$str);
            $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:AW{$h}");
        }
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=完整版.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');

    }

    //返回二维数组中的某二维key的合计
    public function array_sum($arr,$key){
        $sum = 0;
        foreach($arr as $k=>$v){
            $sum += $v[$key];
        }
        return $sum;
    }
}
